﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Net.Mail;
using System.Drawing;
using Microsoft.Office.Interop;
using Microsoft.Office.Tools;
using Office = Microsoft.Office.Interop.Excel;


namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn_rev;
        private delegate void Deleg();
        private DataTable[] DTrev = new DataTable[0];
        private DataTable dt_urs = new DataTable();

        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                conn_rev = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=revenue;Integrated Security=True");
                dateTimePicker1.Value = DateTime.Now.AddDays(-1);

                textBox1.Text = System.Environment.UserName + "@apteka-rayona.ru";

                SqlConnection conn_sec = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=SecurityFA;Integrated Security=True");
                conn_sec.Open();
                SqlCommand em = conn_sec.CreateCommand();
                em.CommandType = CommandType.StoredProcedure;
                em.CommandText = "pas_EmailUser";
                textBox1.Text = em.ExecuteScalar().ToString();
                conn_sec.Close();

                SqlConnection conn_pholapdw = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
                conn_pholapdw.Open();
                SqlCommand urs = conn_pholapdw.CreateCommand();
                urs.CommandType = CommandType.StoredProcedure;
                urs.CommandText = "pas_RegManagerSel";
                dt_urs.Load(urs.ExecuteReader());
                conn_pholapdw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            string procstr = "";
            try
            {
                SqlStorProc.sp_params[] sp = new SqlStorProc.sp_params[1];
                sp[0].name = "@date";
                sp[0].value = dateTimePicker1.Value.ToString("yyyyMMdd");
                DTrev = (new SqlStorProc()).SqlProcExecWithQuery(conn_rev, "pas_CreateRevenueTables_chk_Dinam", 900, sp);
                if (DTrev.Length == 7)
                {
                    DTrev[0].TableName = "ТО";
                    dataGridView1.DataSource = DTrev[0];
                    DTrev[1].TableName = "ССП";
                    dataGridView2.DataSource = DTrev[1];
                    DTrev[2].TableName = "Конвертация";
                    dataGridView3.DataSource = DTrev[2];
                    DTrev[3].TableName = "Клиенты";
                    dataGridView4.DataSource = DTrev[3];
                    DTrev[4].TableName = "Посетители";
                    dataGridView5.DataSource = DTrev[4];
                    DTrev[6].TableName = "Общая";
                    dataGridView6.DataSource = DTrev[6];
                    DTrev[5].TableName = "Чеки";
                    dataGridView7.DataSource = DTrev[5];

                    checkedListBox1.Items.Clear();
                    checkedListBox2.Items.Clear();
                    checkedListBox3.Items.Clear();
                    checkedListBox4.Items.Clear();
                    checkedListBox5.Items.Clear();
                    bool part2 = false;
                    List<string> p3 = new List<string>();
                    for (int i = 0; i < DTrev[0].Columns.Count - 4; i++)
                    {
                        if (i > 2 && i < 7) checkedListBox1.Items.Add(DTrev[0].Columns[i].ColumnName, true);
                        else
                        {
                            int number;
                            if (Int32.TryParse(DTrev[0].Columns[i].ColumnName, out number))
                            {
                                checkedListBox2.Items.Add(DTrev[0].Columns[i].ColumnName, true);
                                part2 = true;
                            }
                            else
                            {
                                if (part2 && DTrev[0].Columns[i].ColumnName.IndexOf("Прирост") == -1)
                                {
                                    p3.Add(DTrev[0].Columns[i].ColumnName);
                                }
                            }
                        }
                    }
                    if (DTrev.Length > 0)
                        for (int i = 0; i < DTrev.Length; i++)
                        {
                            checkedListBox4.Items.Add(DTrev[i].TableName);
                        }
                    if (p3.Count > 0)
                        for (int i = p3.Count - 1; i > -1; i--)
                        {
                            checkedListBox3.Items.Add(p3[i], true);
                        }

                    DataTable urs = DTrev[0].Copy();
                    DataView dv = urs.DefaultView;
                    dv.RowFilter = "Управляющий not in ('','Справка-доставка')";
                    foreach (DataRow dr in dv.ToTable(true, "Управляющий").Rows)
                    {
                        checkedListBox5.Items.Add(dr[0].ToString(), true);
                    }
                    checkBox1.Checked = true;
                    checkBox2.Checked = true;
                    checkBox3.Checked = true;
                    checkBox5.Checked = true;
                    checkBox6.Checked = true;
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(procstr + "\n" + ex.ToString());
            }
            panel1.Enabled = true;
            toolStripStatusLabel1.Text = "";
            toolStripProgressBar1.Value = 1;

        }

        private void button9_Click(object sender, EventArgs e)
        {
            Form2 f = new Form2();
            f.conn = conn_rev;
            f.ShowDialog();
            f.Close();
            f = null;
        }

        private void button10_Click(object sender, EventArgs e)
        {
            Form3 f = new Form3();
            SqlConnection con_count = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=CountDW;Integrated Security=True");
            f.conn = con_count;
            f.ShowDialog();
            f.Close();
            f = null;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if(checkedListBox1.Items.Count > 0)
                for (int i = 0; i < checkedListBox1.Items.Count; i++)
                {
                    checkedListBox1.SetItemChecked(i, (sender as CheckBox).Checked);
                }
        }

        private void checkBox2_CheckedChanged(object sender, EventArgs e)
        {
            if (checkedListBox2.Items.Count > 0)
                for (int i = 0; i < checkedListBox2.Items.Count; i++)
                {
                    checkedListBox2.SetItemChecked(i, (sender as CheckBox).Checked);
                }
        }

        private void checkBox3_CheckedChanged(object sender, EventArgs e)
        {
            if (checkedListBox3.Items.Count > 0)
                for (int i = 0; i < checkedListBox3.Items.Count; i++)
                {
                    checkedListBox3.SetItemChecked(i, (sender as CheckBox).Checked);
                }
        }

        private string CreateExcel(string filename, DataTable[] dt_array)
        {
            int fl_ex = 0;
            string fl_str = "";
            int fl_sh = 0;
            if (dt_array.Length > 0)
            {
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Hashtable myHashtable = new Hashtable();
                myHashtable = CheckExcellProcesses(myHashtable);
                fl_ex = 1;
                Excel.Application exapp = new Excel.Application();
                Excel.Workbook exwb = exapp.Workbooks.Add(System.Reflection.Missing.Value);
                if (exwb.Sheets.Count > 1)
                    for (int i = exwb.Sheets.Count; i > 1; i--)
                        ((Excel.Worksheet)exwb.Sheets[i]).Delete();
                fl_ex = 2;
                Excel.Worksheet exws = (Excel.Worksheet)exwb.Sheets[1];

                exapp.StandardFont = "Calibri";
                exapp.StandardFontSize = 11;

                exapp.DisplayAlerts = false;
                fl_ex = 3;
                try
                {
                    if (filename != "")
                    {
                        int i_sh = 1;
                        for (int t = dt_array.Length - 1; t > -1; t--)
                        {
                            DataTable tbl = dt_array[t].Copy();

                            List<int> new_b = new List<int>();
                            foreach (DataRow row in dt_array[t].Rows)
                            {
                                if (row["Дата открытия"] != DBNull.Value)
                                {
                                    if (Convert.ToDateTime(row["Дата открытия"]).Year >= dateTimePicker1.Value.Year)
                                        new_b.Add(Convert.ToInt32(row[0]));
                                }
                            }

                            bool part2 = false;
                            bool part3 = false;
                            List<string> colname_del = new List<string>();
                            List<int> colname_inf = new List<int>();
                            List<int> colname_p1 = new List<int>();
                            List<int> colname_p2 = new List<int>();
                            List<int> colname_p3 = new List<int>();
                            int colIndInExcel = 1;
                            int colIndInExcel_DateOpen = 0;
                            for (int i = 0; i < tbl.Columns.Count - 1; i++)
                            {
                                bool fl = false;
                                int ord = tbl.Columns[i].Ordinal;
                                string _colname = tbl.Columns[i].ColumnName;
                                if (ord < 7)
                                {
                                    for (int j = 0; j < checkedListBox1.Items.Count; j++)
                                    {
                                        if (checkedListBox1.Items[j].ToString() == _colname)
                                        {
                                            if (!checkedListBox1.GetItemChecked(j)) fl = true;
                                            break;
                                        }
                                    }
                                    if (!fl && _colname == "Дата открытия") colIndInExcel_DateOpen = colIndInExcel;
                                }
                                else
                                {
                                    int number;
                                    string cn = _colname.Replace("Конв ", "").Replace("Клиенты ", "").Replace("Посетит ", "").Replace(", %", "");
                                    if (Int32.TryParse(cn, out number))
                                    {
                                        part2 = true;
                                        for (int j = 0; j < checkedListBox2.Items.Count; j++)
                                        {
                                            if (checkedListBox2.Items[j].ToString() == cn)
                                            {

                                                if (!checkedListBox2.GetItemChecked(j)) fl = true;
                                                break;
                                            }
                                        }
                                    }
                                    else
                                    {
                                        if (part2)
                                        {
                                            part3 = true;
                                            for (int j = 0; j < checkedListBox3.Items.Count; j++)
                                            {
                                                if (checkedListBox3.Items[j].ToString() == cn)
                                                {
                                                    if (!checkedListBox3.GetItemChecked(j))
                                                    {
                                                        fl = true;
                                                    }
                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }
                                if (fl)
                                {
                                    colname_del.Add(_colname);
                                    if (part3)
                                    {
                                        i++;
                                        colname_del.Add(tbl.Columns[i].ColumnName);
                                    }
                                }
                                else
                                {
                                    if (!part2)
                                    {
                                        if (i < 7) colname_inf.Add(colIndInExcel);
                                        else colname_p1.Add(colIndInExcel);
                                    }
                                    else
                                    {
                                        if (!part3) colname_p2.Add(colIndInExcel);
                                        else
                                        {
                                            colname_p3.Add(colIndInExcel);
                                            i++;
                                            colIndInExcel++;
                                            colname_p3.Add(colIndInExcel);
                                        }
                                    }
                                    colIndInExcel++;
                                }
                            }
                            if (colname_del.Count > 0)
                            {
                                for (int i = 0; i < colname_del.Count; i++)
                                {
                                    tbl.Columns.Remove(colname_del[i]);
                                }
                            }
                            fl_ex = 4;
                            ((Excel.Worksheet)exwb.Sheets[1]).Copy(Type.Missing, (Excel.Worksheet)exwb.Sheets[i_sh]);
                            fl_ex = 41;
                            exws = (Excel.Worksheet)exwb.Sheets[i_sh + 1];
                            fl_ex = 42;
                            exws.Name = tbl.TableName;
                            fl_ex = 430;
                            foreach (DataColumn dc in tbl.Columns)
                            {
                                fl_ex++;
                                fl_str = "";
                                fl_sh = i_sh + 1;
                                fl_str = dc.Caption;
                                exws.get_Range(exws.Cells[1, dc.Ordinal + 1], exws.Cells[1, dc.Ordinal + 1]).Value2 = dc.Caption;
                            }
                            fl_str = "";
                            object[,] dt_buf = ToArray(tbl);
                            int rowCount = tbl.Rows.Count;
                            int colCount = colname_p1.Count + colname_p2.Count + colname_p3.Count + colname_inf.Count + 1;
                            fl_ex = 44;
                            exws.get_Range(exws.Cells[2, 1], exws.Cells[tbl.Rows.Count + 1, tbl.Columns.Count]).Value2 = dt_buf;
                            fl_ex = 5;
                            if (colname_inf.Count > 0)
                            {
                                int colst = colname_inf[0];
                                int colend = colname_inf[colname_inf.Count - 1];
                                exws.get_Range(exws.Cells[1, colst], exws.Cells[1, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(79, 129, 189));
                                exws.get_Range(exws.Cells[1, colst], exws.Cells[1, colend]).Cells.Font.Color = Excel.XlRgbColor.rgbWhite;
                                for (int i = 2; i <= rowCount + 1; i++)
                                {
                                    int phcode = Convert.ToInt32(exws.get_Range(exws.Cells[i, 1], exws.Cells[i, 1]).Value2);

                                    if (phcode == 0)
                                    {
                                        //exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbAzure;
                                        exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                    }
                                    else
                                        //if (phcode > 999 && phcode < 2000) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbThistle;
                                        //else
                                        //    if (phcode > 1999 && phcode < 3000) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbPink;
                                        //    else
                                        if (!(i % 2 == 0)) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(220, 230, 241));

                                    int phcode_new_b = Convert.ToInt32(exws.get_Range(exws.Cells[i, 1], exws.Cells[i, 1]).Value2);
                                    int itindex = -1;
                                    bool itfl = false;
                                    foreach (int nb in new_b)
                                    {
                                        itindex++;
                                        if (nb == phcode_new_b)
                                        {
                                            exws.get_Range(exws.Cells[i, 2], exws.Cells[i, 2]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(255, 195, 0));
                                            itfl = true;
                                            break;
                                        }
                                    }
                                    if (phcode > 5200000)
                                        exws.get_Range(exws.Cells[i, 2], exws.Cells[i, 2]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(179, 223, 181));
                                    if (exws.get_Range(exws.Cells[i, colCount - 3], exws.Cells[i, colCount - 3]).Value2 != DBNull.Value)
                                        //- Оранжевый
                                        if (Convert.ToBoolean(exws.get_Range(exws.Cells[i, colCount - 3], exws.Cells[i, colCount - 3]).Value2))
                                            exws.get_Range(exws.Cells[i, 2], exws.Cells[i, 2]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(254, 174, 125));
                                    //- Красный
                                    if (exws.get_Range(exws.Cells[i, colCount - 4], exws.Cells[i, colCount - 4]).Value2 != DBNull.Value)
                                        if (Convert.ToBoolean(exws.get_Range(exws.Cells[i, colCount - 4], exws.Cells[i, colCount - 4]).Value2))
                                            exws.get_Range(exws.Cells[i, 2], exws.Cells[i, 2]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(255, 145, 145));
                                    //- Зелёный
                                    if (exws.get_Range(exws.Cells[i, colCount - 2], exws.Cells[i, colCount - 2]).Value2 != DBNull.Value)
                                        if (Convert.ToBoolean(exws.get_Range(exws.Cells[i, colCount - 2], exws.Cells[i, colCount - 2]).Value2))
                                            exws.get_Range(exws.Cells[i, 2], exws.Cells[i, 2]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(149, 255, 149));
                                    if (checkBox4.Checked)
                                    {
                                        if (exws.get_Range(exws.Cells[i, colCount - 1], exws.Cells[i, colCount - 1]).Value2 != DBNull.Value)
                                            if (Convert.ToBoolean(exws.get_Range(exws.Cells[i, colCount - 1], exws.Cells[i, colCount - 1]).Value2))
                                            {
                                                exws.get_Range(exws.Cells[i, 4], exws.Cells[i, 4]).Cells.Value2 = "!!!";
                                                exws.get_Range(exws.Cells[i, 4], exws.Cells[i, 4]).Cells.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 0, 0));
                                                exws.get_Range(exws.Cells[i, 4], exws.Cells[i, 4]).Cells.Font.Bold = true;
                                                exws.get_Range(exws.Cells[i, 4], exws.Cells[i, 4]).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                                            }
                                    }
                                    else
                                    {
                                        if (exws.get_Range(exws.Cells[i, colCount - 1], exws.Cells[i, colCount - 1]).Value2 != DBNull.Value)
                                            if (Convert.ToBoolean(exws.get_Range(exws.Cells[i, colCount - 1], exws.Cells[i, colCount - 1]).Value2))
                                            {
                                                exws.get_Range(exws.Cells[i, 8], exws.Cells[i, 8]).Cells.Value2 = "!!!";
                                                exws.get_Range(exws.Cells[i, 8], exws.Cells[i, 8]).Cells.Font.Color = ColorTranslator.ToOle(Color.FromArgb(255, 0, 0));
                                                exws.get_Range(exws.Cells[i, 8], exws.Cells[i, 8]).Cells.Font.Bold = true;
                                                exws.get_Range(exws.Cells[i, 8], exws.Cells[i, 8]).Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                                            }
                                    }
                                    if (itfl) new_b.RemoveAt(itindex);
                                }
                            }

                            if (colname_p1.Count > 0)
                            {
                                fl_ex = 6;
                                int colst = colname_p1[0];
                                int colend = colname_p1[colname_p1.Count - 1];
                                exws.get_Range(exws.Cells[1, colst], exws.Cells[1, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(253, 233, 217));
                                exws.get_Range(exws.Cells[1, colst], exws.Cells[1, colend]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                for (int i = 2; i <= rowCount + 1; i++)
                                {
                                    int phcode = Convert.ToInt32(exws.get_Range(exws.Cells[i, 1], exws.Cells[i, 1]).Value2);
                                    if (phcode == 0)
                                    {
                                        //exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbAzure;
                                        exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                    }
                                    else
                                        //if (phcode > 999 && phcode < 2000) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbThistle;
                                        //else
                                        //    if (phcode > 1999 && phcode < 3000) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbPink;
                                        //    else
                                        if (!(i % 2 == 0)) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(220, 230, 241));
                                }
                            }
                            if (colname_p2.Count > 0)
                            {
                                fl_ex = 7;
                                int colst = colname_p2[0];
                                int colend = colname_p2[colname_p2.Count - 1];
                                exws.get_Range(exws.Cells[1, colst], exws.Cells[rowCount + 1, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 153));
                                exws.get_Range(exws.Cells[1, colst], exws.Cells[1, colend]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                for (int i = 2; i <= rowCount + 1; i++)
                                {
                                    int phcode = Convert.ToInt32(exws.get_Range(exws.Cells[i, 1], exws.Cells[i, 1]).Value2);
                                    if (phcode == 0)
                                    {
                                        exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 153));
                                        exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                        break;
                                    }
                                    //    else
                                    //        if (phcode > 999 && phcode < 2000) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbThistle;
                                    //        else
                                    //            if (phcode > 1999 && phcode < 3000) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbPink;
                                    //            else
                                    //                if (i % 2 == 0) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbGold;
                                }
                            }
                            if (colname_p3.Count > 0)
                            {
                                fl_ex = 8;
                                int colst = colname_p3[0];
                                int colend = colname_p3[colname_p3.Count - 1];

                                exws.get_Range(exws.Cells[1, colst], exws.Cells[1, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(79, 129, 189));
                                exws.get_Range(exws.Cells[1, colst], exws.Cells[1, colend]).Cells.Font.Color = Excel.XlRgbColor.rgbWhite;
                                for (int i = 2; i <= rowCount + 1; i++)
                                {
                                    int phcode = Convert.ToInt32(exws.get_Range(exws.Cells[i, 1], exws.Cells[i, 1]).Value2);
                                    if (phcode == 0)
                                    {
                                        exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(253, 233, 217));
                                        exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Font.Color = Excel.XlRgbColor.rgbRed;
                                    }
                                    else
                                        //if (phcode > 999 && phcode < 2000) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbThistle;
                                        //else
                                        //    if (phcode > 1999 && phcode < 3000) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = Excel.XlRgbColor.rgbPink;
                                        //    else
                                        if (!(i % 2 == 0)) exws.get_Range(exws.Cells[i, colst], exws.Cells[i, colend]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(220, 230, 241));
                                }
                                for (int i = colst; i < colend; i += 2)
                                {
                                    exws.get_Range(exws.Cells[1, i], exws.Cells[rowCount + 1, i]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(253, 233, 217));
                                    exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).Cells.Font.Color = Excel.XlRgbColor.rgbBlack;
                                }
                            }

                            for (int i = colname_inf.Count + 1; i <= colCount - 1; i++)
                            {
                                fl_ex = 9;
                                string colnam = exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).Value2.ToString();
                                if (colnam.IndexOf(", %") > -1)
                                {
                                    exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).NumberFormat = "##0.00%";
                                    exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).Value2 = exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).Value2.ToString().Replace(", %", "");
                                    if (colnam.IndexOf("Процент выполнения") > -1)
                                    {
                                        object formul = 0.97;
                                        Excel.FormatCondition cfOR =
                                            (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                        cfOR.Font.Color = Excel.XlRgbColor.rgbRed;

                                        Excel.FormatCondition cfOR2 =
                                            (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                        cfOR2.Font.Color = Excel.XlRgbColor.rgbBlue;
                                    }
                                    else
                                        if (colnam.IndexOf("Прирост") > -1)
                                        {
                                            object formul = 0;
                                            Excel.FormatCondition cfOR =
                                                (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                            cfOR.Font.Color = Excel.XlRgbColor.rgbRed;
                                        }
                                        else
                                            if (colnam.IndexOf("Процент откл вх/вых") > -1)
                                            {
                                                object formul = 0.1;
                                                Excel.FormatCondition cfOR =
                                                    (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                                cfOR.Font.Color = Excel.XlRgbColor.rgbRed;
                                            }
                                                  }
                              
                                else if (colnam.IndexOf("КЧ") > -1)
                                {
                                    //- Формат с плавающей точкой
                                    exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).NumberFormat = "0.00";
                                }
                                else 
                                    exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).NumberFormat = "### ### ### ##0";

                                if (tbl.TableName == "ССП" && colnam == "ССП")
                                {

                                    object formul = "=$" + GetExcelColumnName(i + 1) + "2";
                                    Excel.FormatCondition cfOR =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                    cfOR.Font.Color = Excel.XlRgbColor.rgbRed;
                                    Excel.FormatCondition cfOR2 =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                    cfOR2.Font.Color = Excel.XlRgbColor.rgbBlue;
                                }

                                if (tbl.TableName == "TO" && colnam == "Прогноз на месяц")
                                {

                                    object formul = "=$" + GetExcelColumnName(i + 1) + "2";
                                    Excel.FormatCondition cfOR =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                    cfOR.Font.Color = Excel.XlRgbColor.rgbRed;
                                    Excel.FormatCondition cfOR2 =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                    cfOR2.Font.Color = Excel.XlRgbColor.rgbBlue;
                                }

                                if (tbl.TableName == "TO")
                                {
                                    Excel.FormatCondition cfOR =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[3, i], exws.Cells[3, i]).AddComment("Выручка 36,6");
                                    Excel.FormatCondition cfOR1 =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[6, i], exws.Cells[6, i]).AddComment("Выручка Тесса");
                                }
                                if ((tbl.TableName == "Конвертация" || tbl.TableName == "Общая") && colnam.IndexOf("Средняя конвертация") > -1)
                                {

                                    object formul = "=$" + GetExcelColumnName(i + 1) + "2";
                                    Excel.FormatCondition cfOR =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlLess, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                    cfOR.Font.Color = Excel.XlRgbColor.rgbRed;
                                    Excel.FormatCondition cfOR2 =
                                        (Excel.FormatCondition)exws.get_Range(exws.Cells[2, i], exws.Cells[rowCount + 1, i]).FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, Excel.XlFormatConditionOperator.xlGreater, formul, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                    cfOR2.Font.Color = Excel.XlRgbColor.rgbBlue;
                                }
                                //- Раскраска
                                if (tbl.TableName == "Чеки")
                                {
                                    exws.get_Range(exws.Cells[2, 17], exws.Cells[rowCount + 1, tbl.Columns.Count - 1]).Cells.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(255, 255, 153));
                                }
                              
                            }
                            fl_ex = 10;
                            if (colIndInExcel_DateOpen > 0) exws.get_Range(exws.Cells[2, colIndInExcel_DateOpen], exws.Cells[rowCount + 1, colIndInExcel_DateOpen]).NumberFormat = "dd.MM.yyyy";

                            ((Excel.Worksheet)exwb.Sheets[i_sh]).PageSetup.Zoom = 85;
                            exws.get_Range(exws.Cells[1, colCount], exws.Cells[rowCount + 1, colCount]).Columns.Delete();
                            colCount--;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[rowCount + 1, colCount]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colCount]).RowHeight = Convert.ToInt32(exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colCount]).RowHeight) * 3;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colCount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colCount]).WrapText = true;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colCount]).Cells.Font.Name = "Calibri";
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colCount]).Cells.Font.Size = 11;
                            exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colCount]).EntireColumn.AutoFit();
                            for (int i = 1; i <= colCount; i++)
                                exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).Cells.ColumnWidth = Convert.ToInt32(exws.get_Range(exws.Cells[1, i], exws.Cells[1, i]).Cells.ColumnWidth) * 1.1;
                            //exws.get_Range(exws.Cells[1, 1], exws.Cells[1, colCount]).AutoFilter();
                            fl_ex = 11;
                            if (colname_inf.Count > 0)
                                for (int i = 1; i <= colname_inf.Count; i++)
                                    exws.Application.ActiveWindow.SplitColumn = i;
                            exws.Application.ActiveWindow.SplitRow = 1;
                            exws.Application.ActiveWindow.FreezePanes = true;
                            fl_ex = 12;
                            if (colname_inf.Count > 2)
                                exws.get_Range(exws.Cells[1, colname_inf[2]], exws.Cells[rowCount + 1, colname_inf[colname_inf.Count - 1]]).Group();
                            if ((tbl.TableName != "Общая" && colname_p2.Count > 7) || (tbl.TableName == "Общая" && colname_p2.Count > 21))
                            {
                                if ((tbl.TableName != "Общая" && colname_p2.Count > 7))
                                    exws.get_Range(exws.Cells[1, colname_p2[7]], exws.Cells[rowCount + 1, colname_p2[colname_p2.Count - 1]]).Group();
                                if ((tbl.TableName == "Общая" && colname_p2.Count > 21))
                                    exws.get_Range(exws.Cells[1, colname_p2[21]], exws.Cells[rowCount + 1, colname_p2[colname_p2.Count - 1]]).Group();
                            }
                            if (colname_p3.Count > 0)
                                exws.get_Range(exws.Cells[1, colname_p3[0]], exws.Cells[rowCount + 1, colname_p3[colname_p3.Count - 1]]).Group();
                            if (colname_inf.Count > 2 || colname_p2.Count > 7 || colname_p3.Count > 0)
                            {
                                exws.Outline.SummaryColumn = Excel.XlSummaryColumn.xlSummaryOnLeft;
                                exws.Outline.ShowLevels(Type.Missing, 1);
                            }

                        }
                        fl_ex = 13;
                        ((Excel.Worksheet)exwb.Sheets[1]).Delete();
                        if (checkBox8.Checked == false)
                        {
                            string picturepath = @"\\fs\КОРПОРАТИВНЫЙ ФАЙЛОВЫЙ РЕСУРС\Временная папка, хранение в течении 1 недели\УРС" + @"\\366.png";
                            exws.get_Range(exws.Cells[3, 2], exws.Cells[3, 2]).AddComment(" ");
                            exws.get_Range(exws.Cells[3, 2], exws.Cells[3, 2]).Comment.Shape.Fill.UserPicture(picturepath);
                            exws.get_Range(exws.Cells[3, 2], exws.Cells[3, 2]).Comment.Shape.Width = 250;
                            exws.get_Range(exws.Cells[3, 2], exws.Cells[3, 2]).Comment.Shape.Height = 200;
                            picturepath = @"\\fs\КОРПОРАТИВНЫЙ ФАЙЛОВЫЙ РЕСУРС\Временная папка, хранение в течении 1 недели\УРС" + @"\\tessa.png";
                            exws.get_Range(exws.Cells[5, 2], exws.Cells[5, 2]).AddComment(" ");
                            exws.get_Range(exws.Cells[5, 2], exws.Cells[5, 2]).Comment.Shape.Fill.UserPicture(picturepath);
                            exws.get_Range(exws.Cells[5, 2], exws.Cells[5, 2]).Comment.Shape.Width = 250;
                            exws.get_Range(exws.Cells[5, 2], exws.Cells[5, 2]).Comment.Shape.Height = 200;
                        }
                        exwb.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        exwb.Close(Type.Missing, Type.Missing, Type.Missing);
                        exapp.Quit();
                        KillExcel(myHashtable);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(fl_ex.ToString() + "\n" + fl_sh.ToString() + " \n" + fl_str + "\n" + ex.Message);
                    exapp.Quit();
                    KillExcel(myHashtable);
                }
                finally
                {
                    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                }
            }
            return filename;
        }

        private Excel.XlRgbColor SetColor(object value)
        {
            Excel.XlRgbColor color;

            if (value == DBNull.Value) color = Excel.XlRgbColor.rgbBlueViolet;
            else
            {
                double val = Convert.ToDouble(value);
                if (val < 0) color = Excel.XlRgbColor.rgbRed;
                else
                    if (val < 0.1) color = Excel.XlRgbColor.rgbWhite;
                    else color = Excel.XlRgbColor.rgbYellow;
            }

            return color;
        }

        private Hashtable CheckExcellProcesses(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            int iCount = 0;
            foreach (Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            return myHashtable;
        }

        private void KillExcel(Hashtable myHashtable)
        {
            Process[] AllProcesses = Process.GetProcessesByName("excel");
            foreach (Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }
            AllProcesses = null;
        }

        public static object[,] ToArray(DataTable data)
        {
            var ret = Array.CreateInstance(typeof(object), data.Rows.Count, data.Columns.Count) as object[,];
            for (int i = 0; i < data.Rows.Count; i++)
                for (int j = 0; j < data.Columns.Count; j++)
                    ret[i, j] = data.Rows[i][j];
            return ret;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string fname = "";
                if (checkBox4.Checked)
                {
                    if (checkBox8.Checked) fname = "ДП " + dateTimePicker1.Value.ToString("dd.MM.yyyy") + " (УРС) .xlsx";
                    else fname = "ДП " + dateTimePicker1.Value.ToString("dd.MM.yyyy") + " (легкий).xlsx";
                }
                else fname = "ДП " + dateTimePicker1.Value.ToString("dd.MM.yyyy") + ".xlsx";
                string filename = "";
                DialogResult dr = DialogResult.OK;
                if (filename == "")
                {
                    FileDialog sfd = new SaveFileDialog();
                    sfd.FileName = fname;
                    sfd.Filter = "Excel | *.xlsx";
                    dr = sfd.ShowDialog();
                    if (dr == DialogResult.OK) filename = sfd.FileName;
                    sfd = null;
                }

                if (dr == DialogResult.OK)
                {
                    if (Path.GetExtension(filename) == "xlsx") Path.ChangeExtension(filename, "xlsx");

                    List<DataTable>[] DT_buf;
                    if (checkBox8.Checked)
                    {
                        DT_buf = new List<DataTable>[checkedListBox5.CheckedItems.Count];
                        for (int i = 0; i < checkedListBox5.CheckedItems.Count; i++)
                            DT_buf[i] = new List<DataTable>();
                    }
                    else
                    {
                        DT_buf = new List<DataTable>[1];
                        DT_buf[0] = new List<DataTable>();
                    }

                    string mess_to_show = "";

                    Form5 f5 = new Form5();
                    f5.label1.Text = "Идет формирование файла";
                    f5.label3.Text = "";
                    f5.progressBar1.Value++;
                    f5.progressBar1.Maximum = checkedListBox5.CheckedItems.Count + 1;

                    if (checkedListBox4.CheckedItems.Count > 0)
                    {
                        f5.Show();
                        for (int j = 0; j < DT_buf.Length; j++)
                        {
                            foreach (DataTable dt in DTrev)
                                if (checkedListBox4.Items.Count > 0)
                                    for (int i = 0; i < checkedListBox4.Items.Count; i++)
                                        if (dt.TableName == checkedListBox4.Items[i].ToString() && checkedListBox4.GetItemChecked(i))
                                        {
                                            DataTable newdt = dt.Copy();
                                            DataView dv = newdt.DefaultView;
                                            if (checkBox8.Checked)
                                                dv.RowFilter = "Управляющий like '" + checkedListBox5.CheckedItems[j].ToString() + "'";

                                            DT_buf[j].Add(dv.ToTable());
                                            break;
                                        }

                            if (DT_buf[j].Count > 0)
                            {
                                if (checkBox8.Checked)
                                {
                                    f5.label1.Text = "Идет формирование файла";
                                    f5.label3.Text = checkedListBox5.CheckedItems[j].ToString();
                                    f5.progressBar1.Value++;
                                    f5.label2.Text = (f5.progressBar1.Value - 1).ToString() + " из " + checkedListBox5.CheckedItems.Count;
                                    string new_filename = filename.Replace(fname, checkedListBox5.CheckedItems[j].ToString() + " " + fname);
                                    CreateExcel(new_filename, DT_buf[j].ToArray());
                                    string cc = "";
                                    //if (checkBox11.Checked) cc = textBox1.Text;
                                    if (checkBox9.Checked)
                                    {
                                        string to = "";
                                        for (int i = 0; i < dt_urs.Rows.Count; i++)
                                        {
                                            if (checkedListBox5.CheckedItems[j].ToString() == dt_urs.Rows[i]["Manager"].ToString())
                                            {
                                                to = dt_urs.Rows[i]["Email"].ToString();
                                                cc = "nalexandrina@apteka-rayona.ru";
                                                //to = "ddynaev@apteka-rayona.ru";
                                                break;
                                            }
                                        }
                                        mess_to_show += checkedListBox5.CheckedItems[j].ToString() + "  ";
                                        if (to == "")
                                        {
                                            mess_to_show += "Нет\n";
                                        }
                                        else
                                        {
                                            try
                                            {
                                                SendMail("192.168.0.9", textBox1.Text, "", to, fname.Replace(".xlsx", ""), "Добрый день", new_filename, checkBox10.Checked, checkBox11.Checked);
                                                SendMail("192.168.0.9", textBox1.Text, "", cc, fname.Replace(".xlsx", ""), "Добрый день", new_filename, checkBox10.Checked, checkBox11.Checked);
                                                mess_to_show += "Да\n";
                                            }
                                            catch (Exception ex)
                                            {
                                                MessageBox.Show(ex.Message);
                                                mess_to_show += "Нет\n";
                                            }
                                        }
                                    }

                                }
                                else
                                    CreateExcel(filename, DT_buf[j].ToArray());
                            }
                        }
                    }
                    else MessageBox.Show("Не выбраны вкладки");
                    f5.Close();
                    f5 = null;
                    if (checkBox8.Checked && checkBox9.Checked) MessageBox.Show(mess_to_show, "Отправленные письма");
                    else MessageBox.Show("Файл(ы) сохранен");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        private void checkBox4_CheckedChanged(object sender, EventArgs e)
        {
            if ((sender as CheckBox).Checked)
            {
                checkBox1.Checked = false;
                checkBox3.Checked = false;
                if (checkedListBox2.Items.Count > 7)
                {
                    checkBox2.Checked = false;
                    for (int i = 0; i < 7; i++)
                        checkedListBox2.SetItemChecked(i, true);
                }
                else checkBox2.Checked = true;
                checkBox5.Checked = false;
                for (int i = 0; i < checkedListBox4.Items.Count; i++)
                    if (checkedListBox4.Items[i].ToString() != "Общая") checkedListBox4.SetItemChecked(i, true);
            }
            else
            {
                checkBox1.Checked = true;
                checkBox2.Checked = true;
                checkBox3.Checked = true;
                checkBox5.Checked = true;
            }
        }

        private void checkBox5_CheckedChanged(object sender, EventArgs e)
        {
            if (checkedListBox4.Items.Count > 0)
                for (int i = 0; i < checkedListBox4.Items.Count; i++)
                {
                    checkedListBox4.SetItemChecked(i, (sender as CheckBox).Checked);
                }
        }

        private void checkBox6_CheckedChanged(object sender, EventArgs e)
        {
            if (checkedListBox5.Items.Count > 0)
                for (int i = 0; i < checkedListBox5.Items.Count; i++)
                {
                    checkedListBox5.SetItemChecked(i, (sender as CheckBox).Checked);
                }
        }

        private void checkBox7_CheckedChanged(object sender, EventArgs e)
        {
            if ((sender as CheckBox).Checked)
            {
                checkBox1.Checked = true;
                checkBox2.Checked = true;
                checkBox3.Checked = true;
                checkBox5.Checked = true;
            }
            else
            {
                checkBox1.Checked = false;
                checkBox2.Checked = false;
                checkBox3.Checked = false;
                checkBox5.Checked = false;
            }
        }

        /// Отправка письма на почтовый ящик C# mail send
        /// <param name="smtpServer">Имя SMTP-сервера</param>
        /// <param name="from">Адрес отправителя</param>
        /// <param name="password">пароль к почтовому ящику отправителя</param>
        /// <param name="mailto">Адрес получателя</param>
        /// <param name="caption">Тема письма</param>
        /// <param name="message">Сообщение</param>
        /// <param name="attachFile">Присоединенный файл</param>
        private bool SendMail(string smtpServer, string from, string password, string mailto, string caption, string message, string attachFile, bool del, bool copy_to_me)
        {
            try
            {
                MailMessage mail = new MailMessage();
                mail.From = new MailAddress(from);
                mail.To.Add(new MailAddress(mailto));
                if (copy_to_me) mail.CC.Add(new MailAddress(from));
                mail.Subject = caption;
                mail.Body = message;
                if (!string.IsNullOrEmpty(attachFile))
                    mail.Attachments.Add(new Attachment(attachFile));
                SmtpClient client = new SmtpClient();
                client.Host = smtpServer;
                client.Port = 587;
                client.UseDefaultCredentials = true;
                client.DeliveryMethod = SmtpDeliveryMethod.Network;
                client.Send(mail);
                mail.Dispose();
                if (!string.IsNullOrEmpty(attachFile) && del) File.Delete(attachFile);
                return true;
            }
            catch (Exception e)
            {
                throw new Exception("Mail.Send: " + e.Message);
            }
        }

        private void checkBox8_CheckedChanged(object sender, EventArgs e)
        {
            if ((sender as CheckBox).Checked)
            {
                checkBox9.Checked = true;
                checkBox10.Checked = true;
                button1.Text = "Отправить письма";
            }
            else
            {
                checkBox9.Checked = false;
                checkBox10.Checked = false;
                button1.Text = "Сформировать файл";
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                SqlConnection conn_sec = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=SecurityFA;Integrated Security=True");
                conn_sec.Open();
                SqlCommand emins = conn_sec.CreateCommand();
                emins.CommandType = CommandType.StoredProcedure;
                emins.CommandText = "pas_EmailUpd";
                emins.Parameters.AddWithValue("@email", textBox1.Text);
                if (conn_sec.State == ConnectionState.Closed) conn_sec.Open();
                emins.ExecuteNonQuery();
                conn_sec.Close();
                MessageBox.Show("Адрес электронной почты обновлен");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            Form4 f4 = new Form4();
            f4.server = GetServ("serv.txt");
            f4.ShowDialog();
            f4.Close();
            f4 = null;
        }

        private void checkBox9_CheckedChanged(object sender, EventArgs e)
        {
            //- Кнопка разослать письма
            if (checkBox9.Checked == true)
            {
                button1.Text = "Отправить письма";
            }
            //- Кнопка сформировать файл
            else
            {
                button1.Text = "Сформировать файл";
            }

        }


  
}

}
